tables AS (
        SELECT tbl_name AS table_name, sql
        FROM sqlite_master
        WHERE type = 'table'
          AND name NOT LIKE 'sqlite_%'
    ),

    index_list AS (
        SELECT table_name, name AS index_name, "unique", origin, partial
        FROM tables AS t, pragma_index_list(t.table_name)
    ),
    index_columns AS (
        SELECT il.*, name AS col_name, cid, seqno, "desc", coll, "key"
        FROM index_list AS il, pragma_index_xinfo(il.index_name)
        ORDER BY index_name, seqno
    ),
    noddl_indices AS (
        SELECT table_name, index_name,
               json_group_array(col_name) AS col_names,
               "unique", origin, partial
        FROM index_columns
        GROUP BY index_name
    ),
    indices AS (
        SELECT bi.*, sm.sql
        FROM noddl_indices AS bi, sqlite_master AS sm
        WHERE type = 'index' AND index_name = name
        ORDER BY table_name, index_name
    ),

    fkey_columns AS (
        SELECT table_name AS src_table, "from" AS src_col,
               "table" AS dst_table, "to" AS dst_col,
               on_update, on_delete, id AS fk_id, seq AS fk_seq
        FROM tables AS t,
             pragma_foreign_key_list (t.table_name)
        ORDER BY src_table, fk_id, fk_seq
    ),
    foreign_keys AS (
        SELECT src_table, json_group_array(src_col) AS src_cols,
               dst_table, json_group_array(dst_col) AS dst_cols,
               on_update, on_delete, fk_id
        FROM fkey_columns
        GROUP BY src_table, fk_id
        ORDER BY src_table, dst_table
    ),